﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq.Mapping;
using Microsoft.Data.Sqlite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using KorbeRTR.Utility;

namespace KorbeRTR.Models
{
    /// <summary>
    /// 操作记录
    /// </summary>
    [Table(Name = "OperationRecords")]
    public class OperationRecord
    {
        [Column]
        public Int64 Id { get; set; }
        
        /// <summary>
        /// 操作时间
        /// </summary>
        [Column]
        public DateTime Time { get; set; }

        /// <summary>
        /// 用户ID
        /// </summary>
        [Column]
        public string User { get; set; }

        /// <summary>
        /// 操作内容
        /// </summary>
        [Column]
        public string Operation { get; set; }

        /// <summary>
        /// 操作类型
        /// </summary>
        [Column]
        public string OperationType { get; set; }

        /// <summary>
        /// 软删除标志
        /// </summary>
        [Column]
        public bool IsDeleted { get; set; }

        /// <summary>
        /// 删除n个月前的数据
        /// </summary>
        public static bool DeleteBeforeMonth(DateTime time, int n)
        {
            time = time.AddMonths(-n);
            time = new DateTime(time.Year, time.Month, 1, 0, 0, 0);

            int rows = OperationRecordDBHelper.ExecuteNonQuery("DELETE FROM OperationRecords WHERE Time < @Time",
                new SqliteParameter("@Time", time.ToSqlTime())
                );
            return rows > 0;
        }

        /// <summary>
        /// 添加操作记录
        /// </summary>
        /// <returns></returns>
        public static bool Add(OperationRecord record)
        {
            int rows = OperationRecordDBHelper.ExecuteNonQuery("INSERT INTO OperationRecords(Time,User,Operation,OperationType) VALUES(@Time,@User,@Operation,@OperationType)",
                new SqliteParameter("@Time", record.Time.ToSqlTime()),
                new SqliteParameter("@User", record.User),
                new SqliteParameter("@Operation", record.Operation),
                new SqliteParameter("@OperationType", record.OperationType)
                );
            return rows > 0;
        }

        /// <summary>
        /// 列出全部操作记录
        /// </summary>
        /// <returns></returns>
        public static List<OperationRecord> ListAll(int count=100)
        {
            var dt = OperationRecordDBHelper.ExecuteTable("SELECT * FROM OperationRecords WHERE IsDeleted=FALSE ORDER BY Time DESC LIMIT 0,@NUM",
                new SqliteParameter("@NUM", count));

            var list = new List<OperationRecord>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(dr.ToModel<OperationRecord>());
            }

            return list;
        }

        /// <summary>
        /// 查询 t0 到 t1 时间内的记录
        /// </summary>
        /// <param name="t0">起始时间</param>
        /// <param name="t1">终止时间</param>
        /// <returns></returns>
        public static List<OperationRecord> FilterOperation(DateTime t0, DateTime t1, string text)
        {
            var dt = OperationRecordDBHelper.ExecuteTable("SELECT * FROM OperationRecords WHERE IsDeleted=FALSE AND (Time BETWEEN @T0 AND @T1) AND Operation LIKE @Text",
                new SqliteParameter("@T0", t0.ToSqlTime()),
                new SqliteParameter("@T1", t1.ToSqlTime()),
                new SqliteParameter("@Text", '%' + text + '%')
                );

            //var list = new List<OperationRecord>();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    list.Add(dr.ToModel<OperationRecord>());
            //}

            return dt.ToModel<OperationRecord>();
        }

        /// <summary>
        /// 查询 t0 到 t1 时间内的记录
        /// </summary>
        /// <param name="t0">起始时间</param>
        /// <param name="t1">终止时间</param>
        /// <returns></returns>
        public static List<OperationRecord> Select(DateTime t0, DateTime t1)
        {
            if (t0 > t1)
            {
                AuxLib.Swap(ref t0, ref t1);
            }

            var dt = OperationRecordDBHelper.ExecuteTable("SELECT * FROM OperationRecords WHERE IsDeleted=FALSE AND (Time BETWEEN @T0 AND @T1)",
                new SqliteParameter("@T0", t0.ToSqlTime()),
                new SqliteParameter("@T1", t1.ToSqlTime())
                );

            //var list = new List<OperationRecord>();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    list.Add(dr.ToModel<OperationRecord>());
            //}

            return dt.ToModel<OperationRecord>();
        }

        /// <summary>
        /// 查询N天数据
        /// </summary>
        /// <param name="start">起始日期</param>
        /// <param name="days">天数</param>
        /// <returns></returns>
        public static List<OperationRecord> Select(DateTime aday, int days)
        {
            var t0 = aday.Date;
            var t1 = aday.Date.AddDays(days);
            return Select(t0, t1);
        }

        /// <summary>
        /// 查询某天的数据
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public static List<OperationRecord> Select(DateTime day)
        {
            return Select(day, 1);
        }
    }
}
